--======================================
--  Create INSERT TRIGGER On CUSTOMER
--======================================
USE SBM
GO

IF OBJECT_ID ('TRG_INS_CUSTOMER','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_CUSTOMER 
GO

CREATE TRIGGER TRG_INS_CUSTOMER 
   ON  dbo.CUSTOMER 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = CustomerID from dbo.CUSTOMER order by CustomerID desc
        UPDATE dbo.CUSTOMER SET CustomerID = dbo.FN_NextID (@lastid,'CT',10) where CustomerID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On SAVINGTYPE
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_SAVINGTYPE','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_SAVINGTYE 
GO

CREATE TRIGGER TRG_INS_SAVINGTYPE 
   ON  dbo.SAVINGTYPE 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @Term int
		SELECT @Term =  Term FROM INSERTED

		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = TypeID from dbo.SAVINGTYPE order by right(TypeID, 8) desc 

		IF @Term = -1
			UPDATE dbo.SAVINGTYPE SET TypeID = dbo.FN_NextID (@lastid,'PD',10) where TypeID = ''
		ELSE 
			UPDATE dbo.SAVINGTYPE SET TypeID = dbo.FN_NextID (@lastid,'TD',10) where TypeID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On SAVINGBOOK
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_SAVINGBOOK','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_SAVINGTYE 
GO

CREATE TRIGGER TRG_INS_SAVINGBOOK 
   ON  dbo.SAVINGBOOK 
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @TypeID char(10)
		DECLARE @AccountBalance float
		SELECT @TypeID = TypeID,@AccountBalance=AccountBalance FROM INSERTED
		DECLARE @Term int
		DECLARE @InterestRate float
		SELECT @Term =  Term, @InterestRate = InterestRate FROM SAVINGTYPE WHERE TypeID = @TypeID

		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = BookID from dbo.SAVINGBOOK order by right(BookID, 8) desc 

		IF (@Term = -1)
			UPDATE dbo.SAVINGBOOK SET InterestStartingDate = OpeningDate, SBStatus = 1,AccountBalance=0,
				BookID = dbo.FN_NextID (@lastid,'PS',10) where BookID = ''
		ELSE 
			UPDATE dbo.SAVINGBOOK SET InterestStartingDate = OpeningDate, SBStatus = 1, AccountBalance = @AccountBalance,
				BookID = dbo.FN_NextID (@lastid,'TS',10) where BookID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On DEPOSIT
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_DEPOSIT','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_DEPOSIT 
GO

CREATE TRIGGER TRG_INS_DEPOSIT 
   ON  dbo.DEPOSIT
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON
		DECLARE @BookID char(10)
		DECLARE @DepositDate datetime
		DECLARE @Money int 
		SELECT @DepositDate = DepositingDate, @BookID = BookID, @Money = AmountOfMoney FROM INSERTED

		--Lay thong tin tham so
		DECLARE @MinOfDeposit int
		SELECT TOP 1 @MinOfDeposit = MinOfDeposit FROM dbo.PARAMETER;
		
		IF(@Money < @MinOfDeposit)
			BEGIN
				RAISERROR ('Deposit Money should be greater than %d', 16, 1, @MinOfDeposit)
				ROLLBACK TRANSACTION
			END
		ELSE
			BEGIN
				--lay thong tin so
				DECLARE @TypeID char(10)
				DECLARE @AccBalance bigint
				SELECT @TypeID = TypeID, @AccBalance = AccountBalance FROM dbo.SAVINGBOOK WHERE BookID = @BookID
		
				--Cap nhat chi tiet bao cao ngay
				IF NOT EXISTS(SELECT * FROM dbo.DAILYREPORT WHERE DATEDIFF(DAY,ReportingDate, @DepositDate) = 0)
					INSERT INTO dbo.DAILYREPORT(ReportID, ReportingDate) VALUES ('', @DepositDate)
				DECLARE @ReportID char(10)
				SELECT @ReportID = ReportID FROM dbo.DAILYREPORT WHERE DATEDIFF(DAY,ReportingDate, @DepositDate) = 0

				IF NOT EXISTS(SELECT * FROM dbo.DAILYREPORTDETAIL WHERE ReportID = @ReportID AND SavingTypeID = @TypeID)
					INSERT INTO dbo.DAILYREPORTDETAIL (DetailID, ReportID, SavingTypeID, TotalOfRevenues, TotalOfCost)
						VALUES('', @ReportID, @TypeID, 0, 0)
				DECLARE @DetailID char(10)
				SELECT @DetailID = DetailID FROM dbo.DAILYREPORTDETAIL WHERE ReportID = @ReportID AND SavingTypeID = @TypeID
				UPDATE dbo.DAILYREPORTDETAIL SET TotalOfRevenues = TotalOfRevenues + @Money WHERE DetailID = @DetailID
		
				--Cap nhat chi tiet bao cao thang (neu co)
				IF(@AccBalance = 0)
					BEGIN
						IF NOT EXISTS(SELECT * FROM dbo.MONTHLYREPORT WHERE SavingTypeID = @TypeID AND DATEDIFF(MONTH, ReportingMonth, @DepositDate) = 0)
							INSERT INTO dbo.MONTHLYREPORT(ReportID, ReportingMonth, SavingTypeID)
								VALUES ('', @DepositDate, @TypeID)
						SELECT @ReportID = ReportID FROM dbo.MONTHLYREPORT WHERE SavingTypeID = @TypeID AND DATEDIFF(MONTH, ReportingMonth, @DepositDate) = 0

						IF NOT EXISTS(SELECT * FROM dbo.MONTHLYREPORTDETAIL WHERE ReportID = @ReportID AND DATEDIFF(DAY, ReportingDate, @DepositDate) = 0)
							INSERT INTO dbo.MONTHLYREPORTDETAIL(DetailID, ReportID, ReportingDate, TotalOfOpen, TotalOfClose)
								VALUES('', @ReportID, @DepositDate, 0, 0)
						SELECT @DetailID = DetailID FROM dbo.MONTHLYREPORTDETAIL WHERE ReportID = @ReportID AND DATEDIFF(DAY, ReportingDate, @DepositDate) = 0
						UPDATE dbo.MONTHLYREPORTDETAIL SET TotalOfOpen = TotalOfOpen + 1 WHERE DetailID = @DetailID
					END

				--Danh so tu dong
				DECLARE @LastID nvarchar(10)
				SELECT TOP 1 @LastID = DepositID from dbo.DEPOSIT order by DepositID desc
				UPDATE dbo.DEPOSIT SET DepositID = dbo.FN_NextID (@lastid,'DO',10) where DepositID = ''
		
				--Cap nhat thong tin STK
				DECLARE @NewAccBalance bigint
				EXEC @NewAccBalance = dbo.PROC_Balance @BookID, @DepositDate
				UPDATE dbo.SAVINGBOOK SET InterestStartingDate = @DepositDate,  AccountBalance = @NewAccBalance + @Money
					WHERE BookID = @BookID
			END		
	END
GO

--======================================
--  Create INSERT TRIGGER On WITHDRAW
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_WITHDRAW','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_WITHDRAW 
GO

CREATE TRIGGER TRG_INS_WITHDRAW 
   ON  dbo.WITHDRAW
   AFTER INSERT
AS
	BEGIN		
		SET NOCOUNT ON;
		DECLARE @BookID char(10)
		DECLARE @WithdrawDate datetime
		DECLARE @Money int 
		SELECT @WithdrawDate = WithdrawingDate, @BookID = BookID, @Money = AmountOfMoney FROM INSERTED

		--Lay thong tin tham so
		DECLARE @MinOfWithdraw int
		SELECT TOP 1 @MinOfWithdraw = MinOfWithdraw FROM dbo.PARAMETER;

		--lay thong tin so
		DECLARE @TypeID char(10)
		SELECT @TypeID = TypeID FROM dbo.SAVINGBOOK WHERE BookID = @BookID

		--Tinh so du TK truoc khi rut
		DECLARE @NewAccBalance bigint
		EXEC @NewAccBalance = dbo.PROC_Balance @BookID, @WithdrawDate
		
		IF NOT(@Money BETWEEN @MinOfWithdraw AND @NewAccBalance)
			BEGIN
				RAISERROR ('Withdraw Money should be between %d and %d', 16, 1, @MinOfWithdraw, @NewAccBalance)
				ROLLBACK TRANSACTION
			END
		ELSE
			BEGIN		
				--Cap nhat chi tiet bao cao ngay
				IF NOT EXISTS(SELECT * FROM dbo.DAILYREPORT WHERE DATEDIFF(DAY, ReportingDate, @WithdrawDate) = 0)
					INSERT INTO dbo.DAILYREPORT(ReportID, ReportingDate) VALUES ('', @WithdrawDate)
				DECLARE @ReportID char(10)
				SELECT @ReportID = ReportID FROM dbo.DAILYREPORT WHERE DATEDIFF(DAY, ReportingDate, @WithdrawDate) = 0

				IF NOT EXISTS(SELECT * FROM dbo.DAILYREPORTDETAIL WHERE ReportID = @ReportID AND SavingTypeID = @TypeID)
					INSERT INTO dbo.DAILYREPORTDETAIL (DetailID, ReportID, SavingTypeID, TotalOfRevenues, TotalOfCost)
						VALUES('', @ReportID, @TypeID, 0, 0)
				DECLARE @DetailID char(10)
				SELECT @DetailID = DetailID FROM dbo.DAILYREPORTDETAIL WHERE ReportID = @ReportID AND SavingTypeID = @TypeID
				UPDATE dbo.DAILYREPORTDETAIL SET TotalOfCost = TotalOfCost + @Money WHERE DetailID = @DetailID

				--Danh so tu dong
				DECLARE @LastID nvarchar(10)
				SELECT TOP 1 @LastID = WithdrawID from dbo.WITHDRAW order by WithdrawID desc
				UPDATE dbo.WITHDRAW SET WithdrawID = dbo.FN_NextID (@lastid,'WO',10) where WithdrawID = ''

				--Cap nhat thong tin so		
				UPDATE dbo.SAVINGBOOK SET InterestStartingDate = @WithdrawDate, AccountBalance =  @NewAccBalance - @Money
					WHERE BookID = @BookID

				DECLARE @AccBalance bigint
				SELECT @AccBalance = AccountBalance FROM dbo.SAVINGBOOK WHERE BookID = @BookID
		
				IF(@AccBalance = 0)
					BEGIN
						UPDATE dbo.SAVINGBOOK SET SBStatus = 0
							WHERE BookID = @BookID
				--Cap nhat chi tiet bao cao thang (neu co) 				

						IF NOT EXISTS(SELECT * FROM dbo.MONTHLYREPORT WHERE SavingTypeID = @TypeID AND DATEDIFF(MONTH, ReportingMonth, @WithdrawDate) = 0)
							INSERT INTO dbo.MONTHLYREPORT(ReportID, ReportingMonth, SavingTypeID)
								VALUES ('', @WithdrawDate, @TypeID)
						SELECT @ReportID = ReportID FROM dbo.MONTHLYREPORT WHERE SavingTypeID = @TypeID AND DATEDIFF(MONTH, ReportingMonth, @WithdrawDate) = 0

						IF NOT EXISTS(SELECT * FROM dbo.MONTHLYREPORTDETAIL WHERE ReportID = @ReportID AND DATEDIFF(DAY, ReportingDate, @WithdrawDate) = 0)
							INSERT INTO dbo.MONTHLYREPORTDETAIL(DetailID, ReportID, ReportingDate, TotalOfOpen, TotalOfClose)
								VALUES('', @ReportID, @WithdrawDate, 0, 0)
						SELECT @DetailID = DetailID FROM dbo.MONTHLYREPORTDETAIL WHERE ReportID = @ReportID AND DATEDIFF(DAY, ReportingDate, @WithdrawDate) = 0
						UPDATE dbo.MONTHLYREPORTDETAIL SET TotalOfClose = TotalOfClose + 1 WHERE DetailID = @DetailID
					END
			END
	END
GO

--======================================
--  Create INSERT TRIGGER On DAILYREPORT
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_DAILYREPORT','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_DAILYREPORT
GO

CREATE TRIGGER TRG_INS_DAILYREPORT
   ON  dbo.DAILYREPORT
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = ReportID from dbo.DAILYREPORT order by ReportID desc
        UPDATE dbo.DAILYREPORT SET ReportID = dbo.FN_NextID (@lastid,'DR',10) where ReportID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On DAILYREPORTDETAIL
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_DAILYREPORTDETAIL','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_DAILYREPORTDETAIL
GO

CREATE TRIGGER TRG_INS_DAILYREPORTDETAIL
   ON  dbo.DAILYREPORTDETAIL
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = DetailID from dbo.DAILYREPORTDETAIL order by DetailID desc
        UPDATE dbo.DAILYREPORTDETAIL SET DetailID = dbo.FN_NextID (@lastid,'DD',10) where DetailID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On MONTHLYREPORT
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_MONTHLYREPORT','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_MONTHLYREPORT
GO

CREATE TRIGGER TRG_INS_MONTHLYREPORT
   ON  dbo.MONTHLYREPORT
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = ReportID from dbo.MONTHLYREPORT order by ReportID desc
        UPDATE dbo.MONTHLYREPORT SET ReportID = dbo.FN_NextID (@lastid,'DR',10) where ReportID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On MONTHLYREPORTDETAIL
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_MONTHLYREPORTDETAIL','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_MONTHLYREPORTDETAIL
GO

CREATE TRIGGER TRG_INS_MONTHLYREPORTDETAIL
   ON  dbo.MONTHLYREPORTDETAIL
   AFTER INSERT
AS
	BEGIN
		SET NOCOUNT ON;
		DECLARE @LastID nvarchar(10)
        SELECT TOP 1 @LastID = DetailID from dbo.MONTHLYREPORTDETAIL order by DetailID desc
        UPDATE dbo.MONTHLYREPORTDETAIL SET DetailID = dbo.FN_NextID (@lastid,'DD',10) where DetailID = ''
	END;
GO

--======================================
--  Create INSERT TRIGGER On PARAMETER
--======================================

USE SBM
GO

IF OBJECT_ID ('TRG_INS_PARAMETER','TR') IS NOT NULL
   DROP TRIGGER TRG_INS_PARAMETER
GO

CREATE TRIGGER TRG_INS_PARAMETER
   ON  dbo.PARAMETER
   AFTER INSERT
AS
	SET NOCOUNT ON
	BEGIN
		IF((SELECT COUNT(*) FROM PARAMETER) >1)
			BEGIN
				RAISERROR('Cannot Insert into Parameter', 16,1)
				ROLLBACK TRAN
			END
	END
GO